
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- Crea vista Agente
-- drop view agente

create view [dbo].[agente] as
SELECT 	documento as pers_documento, 
		case when sexo = 1 then 'F' else 'M' end as pers_sexo, 
		null AS cte_tipo_jubilacion,
		case when a.causaBaja = 5 then a.FechaBaja end as agente_fecha_jubilacion,
		(select fechahasta from tb_Antiguedades where  idAgente= a.idAgente and ConceptoAntiguedad = 13) as agente_antig_fecha_docente,
		null AS agente_fecha_opcion,
		(select fechahasta from tb_Antiguedades where  idAgente= a.idAgente and ConceptoAntiguedad = 14) as agente_antig_fecha_admin,
		null AS agente_fecha_inicio_aporte,
		null AS agente_fecha_ult_certificacion,
		null AS agente_fecha_plazo_validez,
		null AS agente_fecha_puntaje,
		null AS agente_porc_retiro,
		null AS agente_puntaje,
		null AS tab_tipo_cobro_id,
		null AS organismo_id,
		null AS tab_zona_salud_id,
		substring(convert(varchar,cuit),1,2) as agente_pre_cuil,
		(select Anios from tb_Antiguedades where  idAgente= a.idAgente and ConceptoAntiguedad = 13) as agente_antig_docente_anos,
		substring(convert(varchar,cuit),11,1) as agente_suf_cuil, 
		(select Anios from tb_Antiguedades where  idAgente= a.idAgente and ConceptoAntiguedad = 14) as agente_antig_admin_anos,
		(select Meses from tb_Antiguedades where  idAgente= a.idAgente and ConceptoAntiguedad = 13) as agente_antig_docente_meses,
		(select Meses from tb_Antiguedades where  idAgente= a.idAgente and ConceptoAntiguedad = 14) as agente_antig_admin_meses,
		null AS agente_percibe_haber_jubilator,
		null AS agente_pension,
		null AS agente_retiro,
		null AS agente_dedicacion_exclu,
		null AS agente_cuenta,
		null AS firma_planilla,
		a.FechaAlta as agente_fecha_alta,
		null AS agente_nro_afiliado,
		case when a.causaBaja = 3 then a.FechaBaja end as agente_fecha_fallece,
		null AS agente_vive,
		null AS agente_cuenta_bonos,
		null AS agente_sucursal_bonos,
		null AS agente_cuenta_lecop,
		null AS agente_sucursal_lecop,
		(select SucursalBancaria from tb_CuentasLiquidacion where Agente = a.idAgente) as sucursal_id
FROM tb_Agentes a

GO
